﻿using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;
using Dapper;

namespace ConsoleAppFramework.Util
{
    public class DapperHelper_SqlServer
    {
        public string ConnectionString { get; private set; }
        public CommandType CommandType { get; set; } = CommandType.Text;
        public bool Buffered { get; set; }
        public int? CommandTimeout { get; set; }

        public DapperHelper_SqlServer(string connectionString)
        {
            ConnectionString = connectionString;
        }

        private IDbConnection OpenConnection()
        {
            var conn = new SqlConnection(ConnectionString);
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            return conn;
        }

        /// <summary>
        /// 查询列表数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public List<T> Query<T>(string sql, object parameters = null)
        {
            using (IDbConnection conn = OpenConnection())
            {
                return conn.Query<T>(sql, parameters, null, Buffered, CommandTimeout, CommandType).ToList();
            }
        }

        /// <summary>
        /// 查询第一行的数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public T QueryFirstOrDefault<T>(string sql, object parameters = null)
        {
            using (IDbConnection conn = OpenConnection())
            {
                return conn.QueryFirstOrDefault<T>(sql, parameters, null, CommandTimeout, CommandType);
            }
        }

        /// <summary>
        /// 查询第一行第一列的数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public T ExecuteScalar<T>(string sql, object parameters = null)
        {
            using (IDbConnection conn = OpenConnection())
            {
                return conn.ExecuteScalar<T>(sql, parameters, null, CommandTimeout, CommandType);
            }
        }

        /// <summary>
        /// 执行增，删，改 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">sql</param>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int Execute(string sql, object parameters = null)
        {
            using (IDbConnection conn = OpenConnection())
            {
                return conn.Execute(sql, parameters, null, CommandTimeout, CommandType);
            }
        }

        /// <summary>
        /// 查询DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataSet QueryDataSet(string sql, object parameters = null)
        {
            DataSet ds = new DataSet();
            using (IDbConnection conn = OpenConnection())
            {
                using (var reader = conn.ExecuteReader(sql, parameters, commandType: CommandType))
                {
                    while (!reader.IsClosed)
                    {
                        ds.Tables.Add().Load(reader);
                    }
                }
            }
            return ds;
        }

        #region 分页查询1
        /// <summary>
        /// 数据分页查询  --支持多表查询  
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="sqlWhere"></param>
        /// <param name="orderBy"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageNo"></param>
        /// <param name="fields"></param>
        /// <param name="parameters"></param>
        /// <returns> table1=总记录数，table2=分页数据  </returns>
        protected DataSet GetDataPageBase(string TableName, string sqlWhere, string orderBy, int pageSize = 10, int pageNo = 1, string fields = "*", object parameters = null)
        {
            DataSet ds = new DataSet();
            if (!string.IsNullOrWhiteSpace(sqlWhere))
            {
                sqlWhere = $" where {ClearAndOr(sqlWhere)}";
            }
            if (!string.IsNullOrWhiteSpace(orderBy))
            {
                orderBy = $" order by {orderBy}";
            }
            TableName = string.IsNullOrWhiteSpace(TableName) ? "table_01" : TableName;

            int begin_index = (pageNo - 1) * pageSize;
            string count_sql = $"select count(*) from {TableName} {sqlWhere} ;";
            ////1.mysql分页limit方法
            //string dt_sql = $"select {fields} from {TableName} {sqlWhere} {orderBy} LIMIT {begin_index},{pageSize} ;";
            if (string.IsNullOrEmpty(orderBy))
            {
                throw new ArgumentNullException("orderBy", "排序字段不能为空");
            }
            //2.sqlserver分页OFFSET...FETCH NEXT, 必须有order by字段 否则报错，仅支持sqlserver2012及以上
            string dt_sql = $"select {fields} from {TableName} {sqlWhere} {orderBy} OFFSET {begin_index} ROWS FETCH NEXT {pageSize} ROWS ONLY ;";
            string sql = $@"
                {count_sql}
                {dt_sql}
            ";
            using (IDbConnection conn = OpenConnection())
            {
                using (var reader = conn.ExecuteReader(sql, parameters, commandType: CommandType))
                {
                    while (!reader.IsClosed)
                    {
                        ds.Tables.Add().Load(reader);
                    }
                }
            }

            return ds;
        }

        /// <summary>
        /// 数据分页查询  --支持多表查询  
        /// </summary>
        /// <param name="TableName">查询的表名</param>
        /// <param name="sqlWhere">sql 查询命令，不要含 where 关键字</param>
        /// <param name="orderBy">排序命令，不要含 order by 关键字</param>
        /// <param name="totalCount">总条数</param>
        /// <param name="fields">字段，默认为*</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="pageNo">页号</param>
        /// <returns></returns>
        public virtual DataTable GetDataPage(string TableName, string sqlWhere, string orderBy, out int totalCount, int pageSize = 10, int pageNo = 1, string fields = "*", object parameters = null)
        {
            DataTable dt = null;
            totalCount = 0;
            DataSet ds = GetDataPageBase(TableName, sqlWhere, orderBy, pageSize, pageNo, fields, parameters);
            if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
            {
                totalCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                dt = ds.Tables[1];
            }
            return dt;
        }

        #endregion

        #region 分页查询2

        /// <summary>
        /// dapper通用分页方法
        /// </summary>
        /// <typeparam name="T">泛型集合实体类</typeparam>
        /// <param name="fields">列</param>
        /// <param name="TableName">表</param>
        /// <param name="sqlWhere">条件</param>
        /// <param name="orderBy">排序</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">当前页显示条数</param>
        /// <param name="totalCount">结果集总数</param>
        /// <returns></returns>
        public IEnumerable<T> GetPageList<T>(string TableName, string sqlWhere, string orderBy, out int totalCount, int pageIndex = 1, int pageSize = 10, string fields = "*", object parameters = null)
        {
            int begin_index = (pageIndex - 1) * pageSize;
            string count_sql = $"select count(*) from {TableName} {sqlWhere} ;";
            ////1.mysql分页limit方法
            //string dt_sql = $"select {fields} from {TableName} {sqlWhere} {orderBy} LIMIT {begin_index},{pageSize} ;";
            if (string.IsNullOrEmpty(orderBy))
            {
                throw new ArgumentNullException("orderBy", "排序字段不能为空");
            }
            //2.sqlserver分页OFFSET...FETCH NEXT, 必须有order by字段 否则报错，仅支持sqlserver2012及以上
            string dt_sql = $"select {fields} from {TableName} {sqlWhere} {orderBy} OFFSET {begin_index} ROWS FETCH NEXT {pageSize} ROWS ONLY ;";
            string sql = $@"
                {count_sql}
                {dt_sql}
            ";

            using (IDbConnection conn = OpenConnection())
            {
                using (var reader = conn.QueryMultiple(sql))
                {
                    totalCount = reader.ReadFirst<int>();
                    return reader.Read<T>();
                }
            }
        }

        #endregion

        #region 辅助方法

        /// <summary>
        /// 清除WHERE开头的 AND 和 OR
        /// </summary>
        /// <param name="where">待处理的where子句（不含where ' AND' or ' OR'开头）</param>
        /// <returns></returns>
        private string ClearAndOr(string where) => Regex.Replace(where.TrimStart(), @"^(AND|OR)", "", RegexOptions.IgnoreCase);
        #endregion

        #region SqlBulkCopy 批量更新数据

        /// <summary>
        /// SqlBulkCopy 批量更新数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">数据集合</param>
        /// <param name="tmpTable">临时表表名</param>
        /// <param name="crateTemplateSql">创建临时表sql脚本</param>
        /// <param name="updateSql">更新数据sql脚本</param>
        public int BulkUpdateData<T>(List<T> list, string tmpTable, string crateTemplateSql, string updateSql)
        {
            var dataTable = ConvertToDataTable(list);
            ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
            using (var conn = new SqlConnection(ConnectionString))
            {
                using (var command = new SqlCommand("", conn))
                {
                    try
                    {
                        if (conn.State != ConnectionState.Open)
                        {
                            conn.Open();
                        }

                        //数据库并创建一个临时表来保存数据表的数据
                        command.CommandText = $"  CREATE TABLE {tmpTable} ({crateTemplateSql})";
                        command.ExecuteNonQuery();

                        //使用SqlBulkCopy 加载数据到临时表中
                        using (var bulkCopy = new SqlBulkCopy(conn))
                        {
                            foreach (DataColumn dcPrepped in dataTable.Columns)
                            {
                                bulkCopy.ColumnMappings.Add(dcPrepped.ColumnName, dcPrepped.ColumnName);
                            }

                            bulkCopy.BulkCopyTimeout = 660;
                            bulkCopy.DestinationTableName = tmpTable;
                            bulkCopy.WriteToServer(dataTable);
                            bulkCopy.Close();
                        }

                        // 执行Command命令 使用临时表的数据去更新目标表中的数据  然后删除临时表
                        command.CommandTimeout = 300;
                        command.CommandText = updateSql;
                        int count = command.ExecuteNonQuery();
                        return count;
                    }
                    catch (Exception ex)
                    {
                        ConsoleHelper.WriteErrorLine(ex.Message);
                        return 0;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }


        /// <summary>
        /// SqlBulkCopy 批量更新数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataTable">数据集合</param>
        /// <param name="tmpTable">临时表表名</param>
        /// <param name="crateTemplateSql">创建临时表sql脚本</param>
        /// <param name="updateSql">更新数据sql脚本</param>
        public int BulkUpdateData(DataTable dataTable, string tmpTable, string crateTemplateSql, string updateSql)
        {
            ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
            using (var conn = new SqlConnection(ConnectionString))
            {
                using (var command = new SqlCommand("", conn))
                {
                    try
                    {
                        if (conn.State != ConnectionState.Open)
                        {
                            conn.Open();
                        }

                        //数据库并创建一个临时表来保存数据表的数据
                        command.CommandText = $"  CREATE TABLE {tmpTable} ({crateTemplateSql})";
                        command.ExecuteNonQuery();

                        //使用SqlBulkCopy 加载数据到临时表中
                        using (var bulkCopy = new SqlBulkCopy(conn))
                        {
                            foreach (DataColumn dcPrepped in dataTable.Columns)
                            {
                                bulkCopy.ColumnMappings.Add(dcPrepped.ColumnName, dcPrepped.ColumnName);
                            }

                            bulkCopy.BulkCopyTimeout = 660;
                            bulkCopy.DestinationTableName = tmpTable;
                            bulkCopy.WriteToServer(dataTable);
                            bulkCopy.Close();
                        }

                        // 执行Command命令 使用临时表的数据去更新目标表中的数据  然后删除临时表
                        command.CommandTimeout = 300;
                        command.CommandText = updateSql;
                        int count = command.ExecuteNonQuery();
                        return count;
                    }
                    catch (Exception ex)
                    {
                        ConsoleHelper.WriteErrorLine(ex.Message);
                        return 0;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

        #endregion

        #region 执行多条SQL语句，实现数据库事务

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="sqlStringList">多条SQL语句</param>        
        public int ExecuteSqlTran(List<string> sqlStringList)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand { Connection = conn };
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    foreach (var strSql in sqlStringList.Where(strSql => strSql.Trim().Length > 1))
                    {
                        cmd.CommandText = strSql;
                        count += cmd.ExecuteNonQuery();
                    }
                    tx.Commit();
                    return count;
                }
                catch (Exception ex)
                {
                    tx.Rollback();
                    return 0;
                }
            }

        }
        /// <summary>
        /// 重载事务提交
        /// </summary>
        /// <param name="sqlStringList"></param>
        /// <returns></returns>
        public int ExecuteSqlTran(ConcurrentBag<string> sqlStringList)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand { Connection = conn };
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    foreach (var strSql in sqlStringList.Where(strSql => strSql.Trim().Length > 1))
                    {
                        cmd.CommandText = strSql;
                        count += cmd.ExecuteNonQuery();
                    }
                    tx.Commit();
                    return count;
                }
                catch (Exception ex)
                {
                    tx.Rollback();
                    return 0;
                }
            }

        }

        #endregion

        #region List转DataTable

        /// <summary>
        /// List转DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <returns></returns>
        public static DataTable ConvertToDataTable<T>(IList<T> data)
        {
            var properties = TypeDescriptor.GetProperties(typeof(T));
            var table = new DataTable();

            foreach (PropertyDescriptor prop in properties)
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);

            foreach (T item in data)
            {
                var row = table.NewRow();

                foreach (PropertyDescriptor prop in properties)
                {
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                }

                table.Rows.Add(row);
            }

            return table;
        }

        #endregion
    }
}
